﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Runtime.InteropServices;  

namespace IntegrationExample
{
    public class WithExcel
    {
        private ApplicationClass xl;
        private object template;
        private _Workbook workbook;
        private object listBef;
        private object listAfter;
        private int numsheets;
        int excelProcessId;

        public WithExcel()
        {
            //Класс для работы с приложением
            xl = new ApplicationClass();
            GetWindowThreadProcessId(xl.Hwnd, ref excelProcessId);
            xl.Visible = false;
            //Тип книги
            template = XlWBATemplate.xlWBATWorksheet;
            //Передача интерфейсу ссылки на созданную книгу
            workbook = xl.Workbooks.Add(template);
            listBef = Missing.Value;
            listAfter = xl.Sheets[1];
            numsheets = 1;
        }

        public WithExcel(string filename)
        {
            xl = new ApplicationClass();

            xl.Visible = false;

            //Передача интерфейсу ссылки на созданную книгу
            workbook = xl.Workbooks.Open(filename, Missing.Value, Missing.Value, 
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            listBef = Missing.Value;
            listAfter = xl.Sheets[1];
            numsheets = 1;
        }

        [DllImport("user32.dll", SetLastError = true)]
        static extern uint GetWindowThreadProcessId(int hWnd, ref int lpdwProcessId);

        /// <summary>
        /// Сохранение документа, закрытие приложения
        /// </summary>
        public void CloseExcel()
        {
            object se = Missing.Value;
            object saveChanges = Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges;
            workbook.Close(saveChanges, se, se);
            workbook = null;
            xl.Workbooks.Close();

            if (xl != null)
            {
                xl.Application.Quit();
                xl.Quit();
            }

            //Marshal.ReleaseComObject(xl);

            xl = null;

            //GC.GetTotalMemory(false);
            //GC.Collect();
            //GC.WaitForPendingFinalizers();
            //GC.Collect();
            //GC.GetTotalMemory(true);

            //try
            //{
            //    Process process = Process.GetProcessById(excelProcessId);
            //    process.Kill();
            //}
            //catch (Exception ex)
            //{  }
        }

        /// <summary>
        /// Передача таблиц в Excel
        /// </summary>
        public void InExcel(List<string[]> str, string nameList)
        {
            xl.Sheets.Add(listBef, listAfter, 1, XlSheetType.xlWorksheet);

            numsheets++;

            xl.ReferenceStyle = XlReferenceStyle.xlR1C1;
            listAfter = xl.Sheets[numsheets];
            _Worksheet worksheet = (_Worksheet)xl.Sheets[numsheets - 1];
            worksheet.Activate();
            worksheet.Name = nameList;
            for (int j = 0; j < str.Count; j++)
                for (int i = 0; i < str[j].Length; i++)
                {
                    xl.Cells[j + 1, i + 1] = str[j][i];
                }
            xl.Columns.AutoFit();
        }

        public int GetLists()
        {
            return xl.Sheets.Count;
        }

        /// <summary>
        /// Получение имен непустых таблиц из Excel
        /// </summary>
        public int GetUnemptyTables(ref string tableName)
        {
            _Worksheet worksheet = (_Worksheet)xl.Sheets[numsheets];
            worksheet.Activate();
            numsheets++;
            tableName = worksheet.Name;
            int i = 1;
            List<string[]> res = new List<string[]>();
            List<string> li = new List<string>();
            while ((xl.Cells[1, i] as Range).Text != "")
            {
                li.Add((xl.Cells[1, i] as Range).Text.ToString());
                i++;
            }
            return li.Count;
        }

        /// <summary>
        /// Получение таблиц из Excel
        /// </summary>
        public List<string[]> FromExcel(ref string tableName, int sheetNumber)
        {
            _Worksheet worksheet = (_Worksheet)xl.Sheets[sheetNumber];
            worksheet.Activate();
            tableName = worksheet.Name;
            int i = 1;
            List<string[]> res = new List<string[]>();
            List<string> li = new List<string>();
            while ((xl.Cells[1, i] as Range).Text != "")
            {
                li.Add((xl.Cells[1, i] as Range).Text.ToString());
                i++;
            }

            if (li.Count > 0)
            {
                res.Add(li.ToArray());
                int countColumns = i;
                int countRows = 1;

                while ((xl.Cells[countRows, 1] as Range).Text.ToString() != "")
                {
                    countRows++;
                }

                for (int j = 2; j < countRows; j++)
                {
                    li = new List<string>();
                    for (int k = 1; k < countColumns; k++)
                    {
                        li.Add((xl.Cells[j, k] as Range).Text.ToString());
                    }
                    res.Add(li.ToArray());
                }
            }
            return res;
        }
    }
}
